package meta

import (
	"fmt"
	"strings"

	"github.com/micro-plat/lib4go/db"
)

// 应用数据库模式
func appSchema(db db.IDBExecuter, tableName string, columns []ColumnDef) error {
	exists, err := tableExists(db, tableName)
	if err != nil {
		return err
	}

	// 表不存在，创建表并添加列
	if !exists {
		return createTable(db, tableName, columns)

	}
	// 表存在，修改列或添加主键
	for _, col := range columns {
		err = modifyOrAddColumn(db, tableName, col)
		if err != nil {
			return err
		}
	}

	return nil
}
func modifyOrAddColumn(db db.IDBExecuter, tableName string, col ColumnDef) error {
	exists, err := columnExists(db, tableName, col.OldName)
	if err != nil {
		return err
	}

	columnDef := buildColumnDefinition(col.Type, col.IsNullable, col.DefaultValue, col.IsAutoIncrement, col.Comment)
	if exists {
		alterStmt := fmt.Sprintf("ALTER TABLE %s change `%s` `%s` %s", tableName, col.OldName, col.Name, columnDef)
		_, err = db.Execute(alterStmt, map[string]interface{}{})
	} else {
		alterStmt := fmt.Sprintf("ALTER TABLE %s ADD COLUMN %s %s", tableName, col.Name, columnDef)
		_, err = db.Execute(alterStmt, map[string]interface{}{})
	}
	if err != nil {
		return fmt.Errorf("修改或添加列 %s 时报错:%w", col.Name, err)
	}
	return nil
}

// 检查表是否存在
func tableExists(db db.IDBExecuter, tableName string) (bool, error) {
	query := fmt.Sprintf("SHOW TABLES LIKE '%s'", tableName)
	rows, err := db.Query(query, map[string]interface{}{})
	if err != nil {
		return false, fmt.Errorf("检查表是否存在时报错:%w", err)
	}
	return rows.Len() > 0, nil
}

// 创建表
func createTable(db db.IDBExecuter, tableName string, columns []ColumnDef) error {
	columnDefs := make([]string, len(columns))
	var primaryKey string
	for i, col := range columns {
		columnDefs[i] = col.Name + " " + buildColumnDefinition(col.Type, col.IsNullable, col.DefaultValue, col.IsAutoIncrement, col.Comment)

		if col.IsAutoIncrement {
			if primaryKey != "" {
				return fmt.Errorf("表 %s 只能有一个自动增长列，并且它必须被定义为键", tableName)
			}
			primaryKey = col.Name
		}
	}
	primaryKeyString := ""
	if primaryKey != "" {
		primaryKeyString = fmt.Sprintf(", PRIMARY KEY (%s)", primaryKey)
	}
	createTableStmt := fmt.Sprintf("CREATE TABLE %s (\n%s%s)", tableName, strings.Join(columnDefs, ",\n "), primaryKeyString)

	_, err := db.Execute(createTableStmt, map[string]interface{}{})
	if err != nil {
		return fmt.Errorf("创建表 %s 时报错:%w", tableName, err)
	}
	return nil
}

// 构建列定义字符串
func buildColumnDefinition(fieldType string, isNullable bool, defaultValue string, isAutoIncrement bool, comment string) string {
	columnDef := fieldType
	if !isNullable {
		columnDef += " NOT NULL"
	}
	if isAutoIncrement {
		columnDef += " AUTO_INCREMENT"
	}
	if strings.HasPrefix(strings.ToUpper(defaultValue), "CURRENT_TIMESTAMP") {
		columnDef += fmt.Sprintf(" DEFAULT %s", defaultValue)
	} else if defaultValue != "" {
		columnDef += fmt.Sprintf(" DEFAULT '%s'", defaultValue)
	}
	if comment != "" {
		columnDef += fmt.Sprintf(" COMMENT '%s'", comment)
	}
	return columnDef
}

// 检查列是否存在
func columnExists(db db.IDBExecuter, tableName, columnName string) (bool, error) {
	query := fmt.Sprintf("SHOW COLUMNS FROM %s LIKE '%s'", tableName, columnName)
	rows, err := db.Query(query, map[string]interface{}{})
	if err != nil {
		return false, fmt.Errorf("检查列是否存在时报错:%w", err)
	}
	return rows.Len() > 0, nil
}
